from hanchlogger import logger
import openpyxl
from openpyxl.cell import MergedCell
from openpyxl.utils import get_column_letter


# 获取某一列的索引号 col_name：查找的列名 row_title：标题所在的行数
def get_col_index(sheet_source, col_name, row_title=1, is_last=0):
    maxColSheetSource = sheet_source.max_column
    # logger.debug('max_column：{}'.format(maxColSheetSource))
    colDest = None
    for colIndex in range(1, maxColSheetSource+1):
        colCellValue = sheet_source.cell(row_title, colIndex).value
        # logger.debug('cur_column：{}'.format(colCellValue))
        if colCellValue is not None:
            colCellValue = '{}'.format(colCellValue).strip()
            if colCellValue == col_name:
                # logger.debug('{} = {} colIndex：{}'.format(colCellValue, col_name, colIndex))
                colDest = colIndex
                if is_last == 0:
                    return colDest
    return colDest


def get_merged_cell_value(sheet_source, row, col):
    cell = sheet_source.cell(row=row, column=col)
    logger.debug('{} - {}'.format(row, col))
    if isinstance(cell, MergedCell):  # 判断该单元格是否为合并单元格
        for merged_range in sheet_source.merged_cell_ranges:  # 循环查找该单元格所属的合并区域
            if cell.coordinate in merged_range:
                # 获取合并区域左上角的单元格作为该单元格的值返回
                cell = sheet_source.cell(row=merged_range.min_row, column=merged_range.min_col)
                break
    else:
        logger.debug('{} - {} 未合并'.format(row, col))
    return cell.value


def merged_cell_value(sheet_source, merge_col_start_index, merge_row_start, merge_row_end, merge_col_end_index=-1):
    colStartIndex = get_column_letter(merge_col_start_index)
    colEndIndex = colStartIndex
    if merge_col_end_index != -1:
        colEndIndex = get_column_letter(merge_col_end_index)
    mergeStr = '{}{}:{}{}'.format(colStartIndex, merge_row_start, colEndIndex, merge_row_end)
    sheet_source.merge_cells(mergeStr)


# 合并 固定的列
def he_bing_cols(sheet_source, col_name_1, col_name_2, clo_name_out, row_start=2):
    maxRowSource = sheet_source.max_row
    colIndex1 = get_col_index(sheet_source, col_name_1)
    colIndex2 = get_col_index(sheet_source, col_name_2)
    colIndexOut = get_col_index(sheet_source, clo_name_out)
    for colHeBing in range(row_start, maxRowSource+1):
        valueColOne = sheet_source.cell(colHeBing, colIndex1).value
        valueColTwo = '{}'.format(sheet_source.cell(colHeBing, colIndex2).value)
        if valueColOne is not None:
            valueColOne = valueColOne.strip()
            if valueColTwo is not None:
                valueColTwo = valueColTwo.strip()
                cellValueOut = valueColOne + '\n' + valueColTwo
                sheet_source.cell(colHeBing, colIndexOut).value = cellValueOut


# 修改特定的承办部门（模糊匹配）
def xiu_gai_cheng_ban_bu_men(sheet_source, col_title, col_source, col_dest, row_start=2):
    colTitleIndex = get_col_index(sheet_source, col_title)
    maxRowSource = sheet_source.max_row
    logger.debug('修改 {} 列 {} 为 {} （模糊匹配）'.format(col_title, col_source, col_dest))
    for rowSource in range(row_start, maxRowSource+1):
        cellValueSearch = sheet_source.cell(rowSource, colTitleIndex).value
        if cellValueSearch is not None:
            cellValueSearch = cellValueSearch.strip()
            valueSplit = cellValueSearch.split(',')
            valueCanYuList = []
            valueDest = ''
            for valueIndex in valueSplit:
                valueIndex = valueIndex.strip()
                if valueIndex.find(col_source) != -1:
                    valueIndex = col_dest
                if valueIndex not in valueCanYuList:
                    if valueDest == '':
                        valueDest = valueIndex
                    else:
                        valueDest = valueDest + ',' + valueIndex
                    valueCanYuList.append(valueIndex)
            sheet_source.cell(rowSource, colTitleIndex).value = valueDest


# 修改特定的承办部门（全词匹配）
def xiu_gai_cheng_ban_bu_men_all(sheet_source, col_title, col_source, col_dest, row_start=2):
    colTitleIndex = get_col_index(sheet_source, col_title)
    maxRowSource = sheet_source.max_row
    logger.debug('修改 {} 列 {} 为 {} （全词匹配）'.format(col_title, col_source, col_dest))
    for rowSource in range(row_start, maxRowSource+1):
        cellValueSearch = sheet_source.cell(rowSource, colTitleIndex).value
        if cellValueSearch is not None:
            cellValueSearch = cellValueSearch.strip()
            valueSplit = cellValueSearch.split(',')
            valueDest = ''
            valueCanYuList = []
            for valueIndex in valueSplit:
                valueIndex = valueIndex.strip()
                if cellValueSearch == col_source:
                    valueIndex = col_dest
                if valueIndex not in valueCanYuList:
                    if valueDest == '':
                        valueDest = valueIndex
                    else:
                        valueDest = valueDest + ',' + valueIndex
                    valueCanYuList.append(valueIndex)
            sheet_source.cell(rowSource, colTitleIndex).value = valueDest


# 根据 ‘col_source’ 匹配 ‘col_dest_1’ 和 ‘clo_dest_2’
def pi_pei_te_ding_col(sheet_source, col_source, sheet_dest, col_dest_1, clo_dest_2, row_start=2):
    maxRowSource = sheet_source.max_row
    colSourceIndex1 = get_col_index(sheet_source, col_source)
    colSourceIndex2 = get_col_index(sheet_source, col_dest_1)
    maxRowDest = sheet_dest.max_row
    colDestIndex1 = get_col_index(sheet_dest, col_source)
    colDestIndex2 = get_col_index(sheet_dest, col_dest_1)
    colSourceIndex3 = 0
    colDestIndex3 = 0
    listAllChengBan = {}
    listAllCanyu = {}
    if clo_dest_2 != '':
        colSourceIndex3 = get_col_index(sheet_source, clo_dest_2)
        colDestIndex3 = get_col_index(sheet_dest, clo_dest_2)
        logger.debug('{} {} {}'.format(clo_dest_2, colSourceIndex3, colDestIndex3))

    for rowIndex in range(2, maxRowDest + 1):
        valueSearchSource = '{}'.format(sheet_dest.cell(rowIndex, colDestIndex1).value).strip()
        colDestIndex2Value = '{}'.format(sheet_dest.cell(rowIndex, colDestIndex2).value).strip()
        listAllChengBan[valueSearchSource] = colDestIndex2Value
        if clo_dest_2 != '':
            colDestIndex3Value = '{}'.format(sheet_dest.cell(rowIndex, colDestIndex3).value).strip()
            listAllCanyu[valueSearchSource] = colDestIndex3Value
        # logger.debug('{} 行 [{}] 承办部门：{} 参与部门:{}'.format(rowIndex, valueSearchSource,
        #                                                          listAllChengBan[valueSearchSource],
        #                                                          listAllCanyu[valueSearchSource]))

    for rowSource in range(row_start, maxRowSource + 1):
        valueSearchSource = sheet_source.cell(rowSource, colSourceIndex1).value
        if valueSearchSource is not None:
            valueSearchSource = valueSearchSource.strip()
            valueSearchSourceJianWei = valueSearchSource + '-1'
            if valueSearchSource in listAllChengBan:
                sheet_source.cell(rowSource, colSourceIndex2).value = listAllChengBan[valueSearchSource]
                if clo_dest_2 != '':
                    if valueSearchSource in listAllCanyu:
                        sheet_source.cell(rowSource, colSourceIndex3).value = listAllCanyu[valueSearchSource]
                # logger.debug('{} 行 [{}] 承办部门：{} 参与部门:{}'.format(rowSource, valueSearchSource,
                #                                                          listAllChengBan[valueSearchSource],
                #                                                          listAllCanyu[valueSearchSource]))
            elif valueSearchSourceJianWei in listAllChengBan:
                sheet_source.cell(rowSource, colSourceIndex2).value = listAllChengBan[valueSearchSourceJianWei]
                if clo_dest_2 != '':
                    if valueSearchSourceJianWei in listAllCanyu:
                        sheet_source.cell(rowSource, colSourceIndex3).value = listAllCanyu[valueSearchSourceJianWei]
            else:
                logger.debug('{} 行 [{}] 未匹配到承办部门'.format(rowSource, valueSearchSourceJianWei))
            # logger.debug('{} 行 [{}] 承办部门：{} 参与部门:{}'.format(rowSource, valueSearchSource, listAllChengBan[valueSearchSource], listAllCanyu[valueSearchSource]))
            # for rowDest in range(row_start, maxRowDest + 1):
            #     valueSearchDest = sheet_dest.cell(rowDest, colDestIndex1).value
            #     if valueSearchDest is not None:
            #         valueSearchDest = valueSearchDest.strip()
            #         if valueSearchSource == valueSearchDest:
            #             colDestIndex2Value = sheet_dest.cell(rowDest, colDestIndex2).value
            #             if colDestIndex2Value is not None:
            #                 colDestIndex2Value = colDestIndex2Value.strip()
            #                 if colDestIndex2Value != '':
            #                     logger.debug('{} {} {}'.format(rowSource, colSourceIndex2, colDestIndex2Value))
            #                     sheet_source.cell(rowSource, colSourceIndex2).value = colDestIndex2Value
            #             if clo_dest_2 != '':
            #                 colDestIndex3Value = sheet_dest.cell(rowDest, colDestIndex3).value
            #                 if colDestIndex3Value is not None:
            #                     colDestIndex3Value = colDestIndex3Value.strip()
            #                     if colDestIndex3Value != '':
            #                         sheet_source.cell(rowSource, colSourceIndex3).value = colDestIndex3Value


# 删除不必要的列
def remove_bu_bi_yao_col(sheet_source, list_bao_liu, row_start=1):
    list_del = []
    max_col = sheet_source.max_column
    for colDel in range(1, max_col + 1):
        colDelName = sheet_source.cell(row_start, colDel).value
        logger.debug('-{}- 列 '.format(colDelName))
        if colDelName in list_bao_liu:
            logger.debug('-{}- 列 保留'.format(colDelName))
        else:
            list_del.append(colDel)
    # 打印删除的列，从最大列开始删除
    list_del.sort(reverse=True)
    logger.debug(list_del)
    for colDel in list_del:
        sheet_source.delete_cols(colDel)


# is_front=True  移动 col_1 到 col_2 前边  is_front=False  移动 col_1 到 col_2 后边
def move_col1_to_col2(sheet_source, col_move, col_flag, is_front=True):
    index_col_flag = get_col_index(sheet_source, col_flag)
    index_col_dest = index_col_flag
    if is_front is not True:
        index_col_dest = index_col_flag + 1
    sheet_source.insert_cols(index_col_dest, 1)

    max_row = sheet_source.max_row
    index_col_move = get_col_index(sheet_source, col_move)
    col_title = get_column_letter(index_col_move)
    colMove = -(index_col_move - index_col_dest)
    colDest = "{}1:{}{}".format(col_title, col_title, max_row + 1)
    sheet_source.move_range(colDest, rows=0, cols=colMove)
    logger.debug('{}:  {}   移动{}列'.format(col_move, get_column_letter(index_col_move), colMove))


def sort_row_by_column_name(row_lst, name):
    name_row = row_lst[0]   # 第一行是各列的名称
    logger.debug('{} '.format(name_row))
    index = name_row.index(name)    # 获得列的索引
    logger.debug('{} 在 {} 列'.format(name, index))
    score_lst = row_lst[1:]   # 用切片操作获得所有分数信息
    logger.debug('{}'.format(score_lst))
    score_lst = sorted(score_lst, key=lambda x: x[index], reverse=False)      # 用指定列的值进行排序
    score_lst.insert(0, name_row)
    return score_lst


def remove_repeat_value(sheet_source, col_src, col_dest, row_start=1):
    max_row_src = sheet_source.max_row
    index_col_src = get_col_index(sheet_source, col_src)
    index_col_dest = get_col_index(sheet_source, col_dest)
    for rowSource in range(row_start, max_row_src + 1):
        valueDest = sheet_source.cell(rowSource, index_col_dest).value
        valueSrc = sheet_source.cell(rowSource, index_col_src).value
        index_col_dest_new = ''
        if valueSrc is not None and valueDest is not None:
            valueDestTrip = valueDest.strip()
            valueSrcTrip = valueSrc.strip()
            valueDestTripList = valueDestTrip.split(',')
            for indexValue in valueDestTripList:
                indexValue = indexValue.strip()
                if indexValue != valueSrcTrip:
                    if index_col_dest_new == '':
                        index_col_dest_new = indexValue
                    else:
                        index_col_dest_new = index_col_dest_new + ',' + indexValue
        sheet_source.cell(rowSource, index_col_dest).value = index_col_dest_new


def pi_pei_cheng_ban_qu_xi_tong(sheet_source, col_source, col_dest_1, col_dest_2, row_start=2):
    maxRowSource = sheet_source.max_row
    colSourceIndexCode = get_col_index(sheet_source, '工单编号')
    colSourceIndex1 = get_col_index(sheet_source, col_source)
    colSourceIndex2 = get_col_index(sheet_source, col_dest_1)
    colSourceIndex3 = get_col_index(sheet_source, col_dest_2)
    for rowSource in range(row_start, maxRowSource + 1):
        valueSearchSource = sheet_source.cell(rowSource, colSourceIndex1).value
        valueCode = sheet_source.cell(rowSource, colSourceIndexCode).value
        if valueSearchSource is not None:
            valueSearchSource = valueSearchSource.strip()
            logger.debug('{} {}'.format(valueCode, valueSearchSource))
            if valueSearchSource != "":
                valueDestTripList = valueSearchSource.split(',')
                valueChengBan = ''
                valueCanYu = ''
                if len(valueDestTripList) > 0:
                    valueChengBan = valueDestTripList[0]
                    valueCanYuList = []
                    for indexValue in valueDestTripList:
                        indexValue = indexValue.strip()
                        if indexValue != valueChengBan and indexValue not in valueCanYuList:
                            if valueCanYu == '':
                                valueCanYu = indexValue
                            else:
                                valueCanYu = valueCanYu + ',' + indexValue
                            valueCanYuList.append(indexValue)
                logger.debug('工单编号：{} {}：{} {}：{} {}:{}'.format(valueCode, col_source, valueSearchSource, col_dest_1, valueChengBan, col_dest_2, valueCanYu))
                if valueChengBan != '':
                    sheet_source.cell(rowSource, colSourceIndex2).value = valueChengBan
                if valueCanYu != '':
                    sheet_source.cell(rowSource, colSourceIndex3).value = valueCanYu


def pi_pei_cheng_ban_qu_xi_tong_2(sheet_source, col_source_1, col_source_2, col_dest_1, col_dest_2, row_start=2):
    maxRowSource = sheet_source.max_row
    colSourceIndexCode = get_col_index(sheet_source, '工单编号')
    colSourceIndex1 = get_col_index(sheet_source, col_source_1)
    colSourceIndex2 = get_col_index(sheet_source, col_source_2)
    colDestIndex1 = get_col_index(sheet_source, col_dest_1)
    colDestIndex2 = get_col_index(sheet_source, col_dest_2)
    if colSourceIndex1 is not None and colSourceIndex2 is not None and colDestIndex1 is not None and \
            colDestIndex2 is not None:
        for rowSource in range(row_start, maxRowSource + 1):
            valueSearchSource1 = sheet_source.cell(rowSource, colSourceIndex1).value
            valueCode = sheet_source.cell(rowSource, colSourceIndexCode).value
            if valueSearchSource1 is not None and valueSearchSource1.strip() != '':
                valueChengBan = valueSearchSource1.strip()
                if valueChengBan != '':
                    sheet_source.cell(rowSource, colDestIndex1).value = valueChengBan
                valueSearchSource2 = sheet_source.cell(rowSource, colSourceIndex2).value
                valueCanYu = ''
                if valueSearchSource2 is not None:
                    valueCanYu = valueSearchSource2.strip()
                if valueCanYu != '':
                    sheet_source.cell(rowSource, colDestIndex2).value = valueCanYu
                logger.debug('工单编号：{} {}：{} {}:{}'.format(valueCode, col_dest_1, valueChengBan, col_dest_2, valueCanYu))


class LeiXingYiJiInfo(object):
    def __init__(self):
        self.nameLeiXing = ''
        self.countZongLiang = 0
        self.countQuPai = 0
        self.countShiPai = 0
        self.countYouXiao = 0
        self.countJieJue = 0
        self.countManYi = 0
        self.countJiBenManYi = 0
        self.listLeiXingErJi = []
        self.listLeiXingErJiArr = {}


class LeiXingErJiInfo(object):
    def __init__(self):
        self.nameLeiXing = ''
        self.countAll = 1
        self.listLeiXingSanJi = []
        self.listLeiXingSanJiArr = {}
        self.listDianWei = []
        self.listDianWeiArr = {}


def get_info_by_lei_xing_qu(sheet_source, row_start=1):
    listLeiXingInFoArr = {}
    listLeiXingInFo = []
    maxRowSource = sheet_source.max_row
    colIndexCode = get_col_index(sheet_source, '工单编号', row_start)
    colIndexLexing1 = get_col_index(sheet_source, '一级分类', row_start)
    colIndexLexing2 = get_col_index(sheet_source, '二级分类', row_start)
    colIndexLexing3 = get_col_index(sheet_source, '三级分类', row_start)
    colIndexXiaQu = get_col_index(sheet_source, '所属辖区', row_start)
    colIndexHuiFang = get_col_index(sheet_source, '回访状态', row_start)
    colIndexJieJue = get_col_index(sheet_source, '是否解决', row_start)
    colIndexManYi = get_col_index(sheet_source, '是否满意', row_start)
    colIndexPaiDan = get_col_index(sheet_source, '派单类型', row_start)
    if colIndexLexing1 is not None and colIndexLexing2 is not None and colIndexLexing3 is not None \
            and colIndexXiaQu is not None and colIndexHuiFang is not None and colIndexJieJue is not None \
            and colIndexManYi is not None and colIndexPaiDan is not None and colIndexCode is not None:
        for rowSource in range(row_start + 1, maxRowSource + 1):
            valueCode = '{}'.format(sheet_source.cell(rowSource, colIndexCode).value).strip()
            valueLexing1 = '{}'.format(sheet_source.cell(rowSource, colIndexLexing1).value).strip()
            valueLexing2 = '{}'.format(sheet_source.cell(rowSource, colIndexLexing2).value).strip()
            valueLexing3 = '{}'.format(sheet_source.cell(rowSource, colIndexLexing3).value).strip()
            valueLXiaQu = '{}'.format(sheet_source.cell(rowSource, colIndexXiaQu).value).strip()
            valueLHuiFang = '{}'.format(sheet_source.cell(rowSource, colIndexHuiFang).value).strip()
            valueLJieJue = '{}'.format(sheet_source.cell(rowSource, colIndexJieJue).value).strip()
            valueLManYi = '{}'.format(sheet_source.cell(rowSource, colIndexManYi).value).strip()
            valueLPaiDan = '{}'.format(sheet_source.cell(rowSource, colIndexPaiDan).value).strip()
            if valueLexing1 != '':
                if valueLexing1 in listLeiXingInFo:
                    leiXingYiJiInfo = listLeiXingInFoArr[valueLexing1]
                else:
                    leiXingYiJiInfo = LeiXingYiJiInfo()
                    listLeiXingInFo.append(valueLexing1)
                leiXingYiJiInfo.countZongLiang = leiXingYiJiInfo.countZongLiang + 1

                if valueLPaiDan == '区派':
                    leiXingYiJiInfo.countQuPai = leiXingYiJiInfo.countQuPai + 1
                elif valueLPaiDan == '市派':
                    leiXingYiJiInfo.countShiPai = leiXingYiJiInfo.countShiPai + 1
                else:
                    logger.error('工单编号：{} 派单类型[{}] 有问题'.format(valueCode, valueLPaiDan))

                if valueLHuiFang == '有效回访':
                    leiXingYiJiInfo.countYouXiao = leiXingYiJiInfo.countYouXiao + 1
                    if valueLJieJue == '解决':
                        leiXingYiJiInfo.countJieJue = leiXingYiJiInfo.countJieJue + 1
                    if valueLManYi == '满意':
                        leiXingYiJiInfo.countManYi = leiXingYiJiInfo.countManYi + 1
                    elif valueLManYi == '基本满意':
                        leiXingYiJiInfo.countJiBenManYi = leiXingYiJiInfo.countJiBenManYi + 1

                # erJiInfo = None
                if valueLexing2 in leiXingYiJiInfo.listLeiXingErJi:
                    erJiInfo = leiXingYiJiInfo.listLeiXingErJiArr[valueLexing2]
                else:
                    leiXingYiJiInfo.listLeiXingErJi.append(valueLexing2)
                    erJiInfo = LeiXingErJiInfo()
                erJiInfo.countAll = erJiInfo.countAll + 1
                if valueLexing3 in erJiInfo.listLeiXingSanJi:
                    erJiInfo.listLeiXingSanJiArr[valueLexing3] = erJiInfo.listLeiXingSanJiArr[valueLexing3] + 1
                else:
                    erJiInfo.listLeiXingSanJi.append(valueLexing3)
                    erJiInfo.listLeiXingSanJiArr[valueLexing3] = 1
                if valueLXiaQu in erJiInfo.listDianWei:
                    erJiInfo.listDianWeiArr[valueLXiaQu] = erJiInfo.listDianWeiArr[valueLXiaQu] + 1
                else:
                    erJiInfo.listDianWei.append(valueLXiaQu)
                    erJiInfo.listDianWeiArr[valueLXiaQu] = 1

                leiXingYiJiInfo.listLeiXingErJiArr[valueLexing2] = erJiInfo
                listLeiXingInFoArr[valueLexing1] = leiXingYiJiInfo
    listWenTiInfoSort = dict(sorted(listLeiXingInFoArr.items(), key=lambda kv: kv[1].countZongLiang, reverse=True))
    # logger.debug('{}'.format(listWenTiInfoSort))
    for index in listWenTiInfoSort:
        logger.debug('{} 接单总量: {}件 '.format(index, listWenTiInfoSort[index].countZongLiang))
        ErInfo = listWenTiInfoSort[index].listLeiXingErJiArr
        # logger.debug('ErInfo: {}'.format(ErInfo))
        ErInfo = dict(sorted(ErInfo.items(), key=lambda kv: kv[1].countAll, reverse=True))
        for indexSanJi in ErInfo:
            ErInfo[indexSanJi].listLeiXingSanJiArr = dict(sorted(ErInfo[indexSanJi].listLeiXingSanJiArr.items(),
                                                                 key=lambda kv: kv[1], reverse=True))
            ErInfo[indexSanJi].listDianWeiArr = dict(sorted(ErInfo[indexSanJi].listDianWeiArr.items(),
                                                            key=lambda kv: kv[1], reverse=True))
            # logger.debug('ErInfo[{}]: {}'.format(indexSanJi, ErInfo[indexSanJi].listLeiXingSanJiArr))
            # logger.debug('ErInfo[{}].listDianWeiArr: {}'.format(indexSanJi, ErInfo[indexSanJi].listDianWeiArr))
    return listWenTiInfoSort

# 处理 考核工单
def get_kao_he_info_by_kao_he_ming_xi(sheet_source, row_start=1):
    listLeiXingInFoArr = {}
    listLeiXingInFo = []
    maxRowSource = sheet_source.max_row
    colChengBanIndex = hanchxl.get_col_index(sheet_source, '承办部门')
    colChengBanLeiXing = hanchxl.get_col_index(sheet_source, '承办单位类型')
    colKaoHeLeiXing = hanchxl.get_col_index(sheet_source, '考核情况')
    colLianXi = hanchxl.get_col_index(sheet_source, '是否联系')
    colJieJue = hanchxl.get_col_index(sheet_source, '是否解决')
    colManYi = hanchxl.get_col_index(sheet_source, '是否满意')
    logger.debug('承办部门：{}列  '.format(colChengBanIndex))
    logger.debug('考核情况：{}列'.format(colLeiXing))
    logger.debug('联系：{}列'.format(colLianXi))
    logger.debug('解决：{}列'.format(colJieJue))
    logger.debug('满意：{}列'.format(colManYi))

    numWeiChuLiKaoHe = 0
    for lineIndex in range(2, maxRowKaoHe + 1):
        valueChengBan = '{}'.format(sheetKaoHe.cell(lineIndex, colChengBanIndex).value).strip().replace('(已删除)', '')
        valueLeiXing = '{}'.format(sheetKaoHe.cell(lineIndex, colLeiXing).value).strip()
        valueLianxi = '{}'.format(sheetKaoHe.cell(lineIndex, colLianXi).value).strip()
        valueJieJue = '{}'.format(sheetKaoHe.cell(lineIndex, colJieJue).value).strip()
        valueManYi = '{}'.format(sheetKaoHe.cell(lineIndex, colManYi).value).strip()
        logger.debug(
            '考核 {}/{} {} {} {} {} {}'.format(lineIndex, maxRowKaoHe, valueChengBan, valueLeiXing, valueLianxi,
                                               valueJieJue, valueManYi))
        if valueChengBan != '' and valueLeiXing != '':
            if valueLeiXing == '只考核响应率':  # 只考核响应率  工单
                if valueChengBan in listChengBan:
                    listChengBan[valueChengBan].kaoHeZongLiang = listChengBan[valueChengBan].kaoHeZongLiang + 1
                    listChengBan[valueChengBan].onlyKaoHe = listChengBan[valueChengBan].onlyKaoHe + 1
                    if valueLianxi == '联系':
                        listChengBan[valueChengBan].onlyLianXi = listChengBan[valueChengBan].onlyLianXi + 1
                    else:
                        listChengBan[valueChengBan].onlyWeiLianXi = listChengBan[valueChengBan].onlyWeiLianXi + 1
                    if listChengBan[index].isKeShi == 1:
                        sheetKaoHe.cell(lineIndex, 1).value = '已处理-科室'
                    else:
                        sheetKaoHe.cell(lineIndex, 1).value = '已处理-社区村'
                else:
                    logger.debug('考核 {}/{} 未找到承办部门'.format(lineIndex, maxRowKaoHe))
                    sheetKaoHe.cell(lineIndex, 1).value = '未处理-未找到承办部门'
                    numWeiChuLiKaoHe = numWeiChuLiKaoHe + 1
            elif valueLeiXing == '考核三率':  # 考核三率 工单
                if valueChengBan in listChengBan:
                    listChengBan[valueChengBan].kaoHeZongLiang = listChengBan[valueChengBan].kaoHeZongLiang + 1
                    listChengBan[valueChengBan].sanKaoHe = listChengBan[valueChengBan].sanKaoHe + 1
                    if valueLianxi == '联系' or valueLianxi == '已联系' or valueLianxi == '是':
                        listChengBan[valueChengBan].sanLianXi = listChengBan[valueChengBan].sanLianXi + 1
                    if valueLianxi == '未联系' or valueLianxi == '否':
                        listChengBan[valueChengBan].sanWeiLianXi = listChengBan[valueChengBan].sanWeiLianXi + 1
                    if valueJieJue == '解决' or valueJieJue == '已解决' or valueJieJue == '是':
                        listChengBan[valueChengBan].sanJieJue = listChengBan[valueChengBan].sanJieJue + 1
                    if valueJieJue == '未解决' or valueJieJue == '否':
                        listChengBan[valueChengBan].sanWeiJieJue = listChengBan[valueChengBan].sanWeiJieJue + 1
                    if valueManYi == '满意' or valueManYi == '非常满意':
                        listChengBan[valueChengBan].sanManYi = listChengBan[valueChengBan].sanManYi + 1
                    if valueManYi == '基本满意':
                        listChengBan[valueChengBan].sanJiBenManYi = listChengBan[valueChengBan].sanJiBenManYi + 1
                    if valueManYi == '不满意' or valueManYi == '非常不满意':
                        listChengBan[valueChengBan].sanBuManYi = listChengBan[valueChengBan].sanBuManYi + 1
                    if listChengBan[index].isKeShi == 1:
                        sheetKaoHe.cell(lineIndex, 1).value = '已处理-科室'
                    else:
                        sheetKaoHe.cell(lineIndex, 1).value = '已处理-社区村'
                else:
                    logger.debug('考核 {}/{} 未找到承办部门'.format(lineIndex, maxRowKaoHe))
                    sheetKaoHe.cell(lineIndex, 1).value = '未处理-未找到承办部门'
                    numWeiChuLiKaoHe = numWeiChuLiKaoHe + 1

